Single-Row Functions vs. Aggregate Functions in MySQL
MySQL functions can be grouped into two major types based on how they operate on data: single-row functions and aggregate functions. They differ in purpose, behavior, and how many rows they process.
Single-row functions operate on one row at a time.
For each input row, they return exactly one output value.
They do not reduce the number of rows in the result set.
Used in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING clauses.
Categories include string, numeric, date/time, conversion, and control functions.
Aggregate functions operate on multiple rows and return a single summarized result.
Used to perform calculations on groups of rows (e.g., totals, averages).
Often used with GROUP BY to produce results per group.
If GROUP BY is not used, the entire table is treated as one group.
Rows processed: Single-row functions process one row at a time; aggregate functions process multiple rows at once.
Output: Single-row functions return one result per row; aggregate functions return one result per group.
Usage: Aggregate functions require GROUP BY if you want results per group; single-row functions do not.
Effect on rows: Single-row functions do not change the number of rows; aggregate functions reduce rows.
In summary, single-row functions transform individual rows, while aggregate functions summarize multiple rows into a single result.